package JDBCwork;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class PeopleService implements PeopleServie{

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/stu", "root", "root");

        } catch (Exception e) {
            System.out.println(e);
        }
    }

    @Override
    public boolean add(People people) {
        Connection connection=null;
        PreparedStatement statement=null;
        try {

            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/stu","root","root");

            String sql = "INSERT INTO people(name,gander,date,hight) VALUES (?,?,?,?)";
            statement = connection.prepareStatement(sql); //预编译
            statement.setString(1,people.getName());
            statement.setString(2,people.getGander());
            statement.setDate(3,new java.sql.Date(people.getDate().getTime()));
            statement.setDouble(4,people.getHight());
            int n = statement.executeUpdate();//
            if (n>0){
                return true;
            }
        }catch (Exception e){
            System.out.println(e.getMessage());
        }finally { //关闭(不管成功与否都会关闭)
            try{
                statement.close();
                connection.close();
            }catch (Exception e){
                System.out.println(e.getMessage());
            }
        }
        return false;
    }

    @Override
    public boolean update(People people) {
        try {
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/stu","root","root");
            //2.写sql语句(查询所有学生)
            String sql = "UPDATE people SET date =?  hight = ? WHERE id = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setDate(1,new java.sql.Date(people.getDate().getTime()));
            preparedStatement.setDouble(2,people.getHight());
            preparedStatement.setString(3,people.getNum());

            int i = preparedStatement.executeUpdate();
            if (i>0){
                return true;
            }else {
                return false;
            }


        }catch (Exception e){
            return false;
        }
    }

    @Override
    public boolean delete(People people) {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/stu","root","root");
            String sql = "delete from people where num=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,people.getNum());
            preparedStatement.executeUpdate();
            return true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //2.写sql语句(查询所有学生)
        return false;
    }



    @Override
    public People findByName(String name) {
        try {
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/stu","root","root");
            //2.写sql语句(查询所有学生)
            String sql = "SELECT * FROM student where name = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,name);
            ResultSet set = preparedStatement.executeQuery();
            while (set.next()){
                String num=set.getString("num");
                String name1=set.getString("name");
                String gander=set.getString("gander");
                Date date=set.getDate("date");
                double hight=set.getDouble("hight");
                return  new People(num,name1,gander,date,hight);
            }
            //关闭
            set.close();
            preparedStatement.close();
            connection.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<People> findAll() throws SQLException {
        List <People>list=new ArrayList();

        Connection connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/people", "root", "root");
        String sql="select * from people";

        Statement statement = connection.createStatement();
        ResultSet set = statement.executeQuery(sql);
        while(set.next()){
            String num=set.getString("num");
            String name=set.getString("name");
            String gander=set.getString("gander");
            Date date=set.getDate("date");
            double hight=set.getDouble("hight");

            list.add(new People(num,name,gander,date,hight));

        }
        set.close();
        set.close();
        connection.close();
        if(list.size()==0){
            return null;
        }
        return list;
    }
}